package ua.kharkov.khpi.tarasenko.bach.dao.mysql.item;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import ua.kharkov.khpi.tarasenko.bach.core.Constant;
import ua.kharkov.khpi.tarasenko.bach.core.db.DBUtils;
import ua.kharkov.khpi.tarasenko.bach.core.db.EShopDataStoreException;
import ua.kharkov.khpi.tarasenko.bach.dao.core.item.ItemDAO;
import ua.kharkov.khpi.tarasenko.bach.dao.core.item.ItemDTO;

/**
 * DAO for working with table with items in Derby database.
 * 
 * @author oleksandr
 * 
 */
public class MysqlItemDAO implements ItemDAO {
	private static final Logger log = Logger.getLogger(MysqlItemDAO.class);

	// ===========================================================================
	// Table column's names
	// ===========================================================================
	public static final String COLUMN_ID = "item_id";
	public static final String COLUMN_NAME = "item_name";
	public static final String COLUMN_CATEGORY = "item_category";
	public static final String COLUMN_QUANTITY = "item_quantity";
	public static final String COLUMN_PRICE = "item_price";
	public static final String COLUMN_DESCRIPTION = "item_description";

	// ===========================================================================
	// QUERIES
	// ===========================================================================
	public static final String QUERY_FIND_ALL_ITEMS = "SELECT * FROM "
			+ Constant.TABLE_ITEM;
	public static final String QUERY_FIND_ITEM_BY_ID = "SELECT * FROM "
			+ Constant.TABLE_ITEM + " WHERE " + COLUMN_ID + "= ?";
	public static final String QUERY_FIND_ITEMS_BY_CATEGORY = "SELECT * FROM "
			+ Constant.TABLE_ITEM + " WHERE " + COLUMN_CATEGORY + "= ?";
	public static final String QUERY_ADD_ITEM = "INSERT INTO "
			+ Constant.TABLE_ITEM + "(" + COLUMN_NAME + ", " + COLUMN_CATEGORY
			+ ", " + COLUMN_QUANTITY + ", " + COLUMN_PRICE + ", "
			+ COLUMN_DESCRIPTION + ") VALUES(?, ?, ?, ?, ?)";
	public static final String QUERY_UPDATE_ITEM = "UPDATE "
			+ Constant.TABLE_ITEM + " SET " + COLUMN_NAME + "=?, "
			+ COLUMN_CATEGORY + "=?," + COLUMN_QUANTITY + "=?," + COLUMN_PRICE
			+ "=?," + COLUMN_DESCRIPTION + "=? WHERE " + COLUMN_ID + "=?";
	public static final String QUERY_DELETE_ITEM = "DELETE FROM " + Constant.TABLE_ITEM + " WHERE "
			+ COLUMN_ID + "=?";

	private Connection conn;

	/**
	 * Creates entity if the DerbyItemDAO.
	 * 
	 * @param conn
	 *            - connection to the database.
	 * @throws EShopDataStoreException
	 *             if connection is null.
	 */
	public MysqlItemDAO(Connection conn) throws EShopDataStoreException {
		if (conn == null) {
			throw new EShopDataStoreException("Connection is null!");
		}
		this.conn = conn;
	}

	@Override
	public ItemDTO findItemById(int id) throws EShopDataStoreException {
		if (log.isTraceEnabled()) {
			log.trace("Item's id to find is " + id);
		}

		ResultSet rs = null;
		PreparedStatement ps = null;
		ItemDTO item = null;
		try {
			ps = conn.prepareStatement(QUERY_FIND_ITEM_BY_ID);
			ps.setString(1, String.valueOf(id));
			rs = ps.executeQuery();
			if (rs.next()) {
				item = extractItem(rs);
			}
			if (log.isDebugEnabled()) {
				log.debug("found item:\n" + item);
			}
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during looking for item with id = " + id, e);
		} finally {
			DBUtils.close(rs, ps);
		}
		return item;
	}

	@Override
	public ArrayList<ItemDTO> findAllItems()
			throws EShopDataStoreException {
		ArrayList<ItemDTO> items = new ArrayList<ItemDTO>();
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(QUERY_FIND_ALL_ITEMS);
			rs = ps.executeQuery();
			ItemDTO item = null;
			while (rs.next()) {
				item = extractItem(rs);
				items.add(item);
			}
			if (log.isDebugEnabled()) {
				log.debug("Found " + items.size() + " items.");
			}
			if (log.isTraceEnabled()) {
				log.trace("Found items:\n" + items);
			}
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during looking for all items", e);
		} finally {
			DBUtils.close(rs, ps);
		}
		return items;
	}

	@Override
	public ArrayList<ItemDTO> findItemsByCategory(int categoryId)
			throws EShopDataStoreException {
		ArrayList<ItemDTO> items = new ArrayList<ItemDTO>();
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(QUERY_FIND_ITEMS_BY_CATEGORY);
			ps.setInt(1, categoryId);
			rs = ps.executeQuery();
			ItemDTO item = null;
			while (rs.next()) {
				item = extractItem(rs);
				items.add(item);
			}
			if (log.isDebugEnabled()) {
				log.debug("Found " + items.size() + " items.");
			}
			if (log.isTraceEnabled()) {
				log.trace("Found items:\n" + items);
			}
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during looking items from category="
							+ categoryId, e);
		} finally {
			DBUtils.close(rs, ps);
		}
		return items;
	}

	@Override
	public int addItem(ItemDTO item) throws EShopDataStoreException {
		if (log.isTraceEnabled()) {
			log.trace("Item to add:\n" + item);
		}

		PreparedStatement ps = null;
		int rowsAdded = 0;
		try {
			ps = conn.prepareStatement(QUERY_ADD_ITEM,
					Statement.RETURN_GENERATED_KEYS);
			ps.setString(1, item.getName());
			ps.setInt(2, item.getCategory());
			ps.setInt(3, item.getQuantity());
			ps.setDouble(4, item.getPrice());
			ps.setString(5, item.getDescription());
			rowsAdded = ps.executeUpdate();
			ResultSet rs = ps.getGeneratedKeys();
			int newId = 0;
			if (rs.next()) {
				newId = rs.getInt(1);
			}
			if (log.isTraceEnabled()) {
				log.trace("addded " + rowsAdded + " items");
			}
			if (log.isDebugEnabled()) {
				log.debug("new item's id: " + newId);
			}
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during adding new item with info:\n" + item,
					e);
		} finally {
			DBUtils.close(null, ps);
		}
		return rowsAdded;
	}

	@Override
	public int updateItem(ItemDTO item) throws EShopDataStoreException {
		if (log.isTraceEnabled()) {
			log.trace("Item to update:\n" + item);
		}

		PreparedStatement ps = null;
		int rowsUpdated = 0;
		try {
			ps = conn.prepareStatement(QUERY_UPDATE_ITEM);
			ps.setString(1, item.getName());
			ps.setInt(2, item.getCategory());
			ps.setInt(3, item.getQuantity());
			ps.setDouble(4, item.getPrice());
			ps.setString(5, item.getDescription());

			int itemId = item.getId();
			ps.setInt(6, itemId);

			rowsUpdated = ps.executeUpdate();
			if (log.isDebugEnabled()) {
				log.debug("Updated item's id=" + itemId + ", rows updated="
						+ rowsUpdated);
			}
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during updating item with info:\n" + item,
					e);
		} finally {
			DBUtils.close(null, ps);
		}
		return rowsUpdated;
	}

	@Override
	public int deleteItem(int itemId) throws EShopDataStoreException {
		if (log.isTraceEnabled()) {
			log.trace("Item's id to delete is " + itemId);
		}

		int rowsDeleted = 0;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(QUERY_DELETE_ITEM);
			ps.setInt(1, itemId);

			rowsDeleted = ps.executeUpdate();
			if (log.isDebugEnabled()) {
				log.debug("Item with id=" + itemId + " deleted. Rows deleted="
						+ rowsDeleted);
			}
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during deleting item with id=" + itemId, e);
		} finally {
			DBUtils.close(null, ps);
		}

		return rowsDeleted;
	}

	/**
	 * Extract item from result set.
	 * 
	 * @param rs
	 *            - result set with data.
	 * @return extracted item.
	 * @throws SQLException
	 *             if error occurred.
	 */
	private ItemDTO extractItem(ResultSet rs) throws SQLException {
		ItemDTO item = new ItemDTO();
		item.setId(rs.getInt(COLUMN_ID));
		item.setName(rs.getString(COLUMN_NAME));
		item.setPrice(rs.getDouble(COLUMN_PRICE));
		item.setQuantity(rs.getInt(COLUMN_QUANTITY));
		item.setDescription(rs.getString(COLUMN_DESCRIPTION));
		item.setCategory(rs.getInt(COLUMN_CATEGORY));
		return item;
	}

}
